<?php if (!defined('BASEPATH')) {exit('No direct script access allowed');}

class mprocurement extends CI_Model {

	function __construct() {
		parent::__construct();
		 $ci = & get_instance();
         $ci->load->model('mdealer');	
	}
	
	function get_data($p1="",$p2=""){
		$where=" WHERE 1=1 ";
		switch ($p1){
			case "pemesanan_cabang":
				$id=$this->input->post('id');
				$cat=$this->input->post('cat');
				$key=$this->input->post('key');
				$tgl_awal=$this->input->post('tgl_awal');
				$tgl_akhir=$this->input->post('tgl_akhir');
				$status=$this->input->post('status');
				
				if($tgl_awal){
					$where .=" AND A.tgl_pemesanan BETWEEN '".$tgl_awal."' AND '".$tgl_akhir."'";
				}
				if($status!=""){
					$where .=" AND A.flag ='".$status."'";
				}
				if($cat!=""){
					$where .=" AND ".$cat." like '%".$key."%'";
				}
				
				if($id){$where .=" AND A.id=".$id;}
				$sql="SELECT A.*,B.nama_dealer FROM tbl_pemesanan_cabang A
					LEFT JOIN tbl_master_dealer B ON A.id_dealer=B.id ".$where." 
					AND A.flag_div='".$this->input->post('flag_div')."'";
				if($id){return $this->db->query($sql)->row();}
			break;
			case "pemesanan":
				
				$id=$this->input->post('id');
				$cat=$this->input->post('cat');
				$key=$this->input->post('key');
				$tgl_awal=$this->input->post('tgl_awal');
				$tgl_akhir=$this->input->post('tgl_akhir');
				$status=$this->input->post('status');
				
				if($tgl_awal){
					$where .=" AND A.tgl_pemesanan BETWEEN '".$tgl_awal."' AND '".$tgl_akhir."'";
				}
				if($status!=""){
					$where .=" AND A.flag ='".$status."'";
				}
				if($cat!=""){
					$where .=" AND ".$cat." like '%".$key."%'";
				}
				if($id){$where .=" AND A.id=".$id;}
				
					$sql="SELECT Z.no_pemesanan as no_permintaan,Z.tgl_pemesanan as tgl_permintaan,A.id as id_pemesanan_supplier,A.id,A.no_pemesanan,A.tgl_pemesanan,B.nama_supplier,G.nama_kabupaten,H.nama_kecamatan,
					I.nama_kelurahan,B.alamat,B.no_telp,B.email,A.pemesanan_via,A.sistem_pengiriman,A.flag,Z.id as id_cabang,A.kode_supplier,
					CASE WHEN A.flag_div='U' THEN 'Divisi Unit'
					WHEN A.flag_div='S' THEN 'Divisi Spare Part'
					WHEN A.flag_div='M' THEN 'Divisi Marketing'
					ELSE 'Divisi Not Member'
					END AS DIVISI
					FROM tbl_pemesanan_cabang Z 
					LEFT JOIN tbl_pemesanan_supplier A ON A.tbl_pemesanan_cabang_id=Z.id 
					LEFT JOIN tbl_master_supplier B ON A.kode_supplier=B.id
					LEFT JOIN idx_group_supplier D ON B.id_group_supplier=D.id
					LEFT JOIN tbl_master_wilayah E ON B.id_wilayah=E.id
					LEFT JOIN idx_wilayah_provinsi F ON E.id_provinsi=F.id
					LEFT JOIN idx_wilayah_kabupaten G ON E.id_kabupaten=G.id_kabupaten
					LEFT JOIN idx_wilayah_kecamatan H ON E.id_kecamatan=H.id
					LEFT JOIN idx_wilayah_kelurahan I ON E.id_kelurahan=I.id
					".$where." AND Z.flag_div='".$this->input->post('flag_div')."' AND Z.flag <> 'D'";
					
				if($id){return $this->db->query($sql)->row();}
			break;
			case "detil_pemesanan_cabang":
				if($p2!=''){
					$where .=" AND A.flag='P'";
				}
				$sql="SELECT A.*,B.nama_group_barang,C.kategori_barang,D.nominal_harga,D.ppn,Z.nama_barang,
				Z.merek_barang,Z.satuan_barang,(A.qty*D.nominal_harga) as tot_harga,(A.qty*D.nominal_harga)+D.ppn as tot_ppn
				FROM tbl_detil_pemesanan_cabang A
				LEFT JOIN tbl_master_barang Z ON A.tbl_master_barang_id=Z.id
				LEFT JOIN idx_group_barang B ON Z.id_group_barang=B.id
				LEFT JOIN idx_kategori_barang C ON Z.id_kategori_barang=C.id
				LEFT JOIN (SELECT * FROM tbl_master_harga_barang WHERE type_harga=2)D ON D.id_barang=Z.id
				".$where." AND A.tbl_pemesanan_cabang_id=".($p2!='' ? $p2 : $this->input->post('id'));
				
				return $this->db->query($sql)->result_array();
			break;
			case "detil_pemesanan":
				$sql="SELECT A.*,B.nama_group_barang,C.kategori_barang,D.nominal_harga,D.ppn,Z.nama_barang,
				Z.merek_barang,Z.satuan_barang,(A.qty*D.nominal_harga) as tot_harga,(A.qty*D.nominal_harga)+D.ppn as tot_ppn,
				F.qty as qty_cabang
					FROM tbl_detil_pemesanan_supplier A
					LEFT JOIN tbl_pemesanan_supplier D ON A.tbl_master_pemesanan_supplier=D.id
					LEFT JOIN tbl_pemesanan_cabang E ON D.tbl_pemesanan_cabang_id=E.id
					LEFT JOIN tbl_detil_pemesanan_cabang F ON F.tbl_pemesanan_cabang_id=E.id AND A.tbl_master_barang_id=F.tbl_master_barang_id
					LEFT JOIN tbl_master_barang Z ON A.tbl_master_barang_id=Z.id
					LEFT JOIN idx_group_barang B ON Z.id_group_barang=B.id
					LEFT JOIN idx_kategori_barang C ON Z.id_kategori_barang=C.id
					LEFT JOIN (SELECT * FROM tbl_master_harga_barang WHERE type_harga=2)D ON D.id_barang=Z.id
				WHERE A.tbl_master_pemesanan_supplier=".($p2!='' ? $p2 : $this->input->post('id'));
				//echo $sql;
				return $this->db->query($sql)->result_array();
			break;
			
			
			
		
			case "pembelian":
				$id=$this->input->post('id');
				$where .=" AND A.flag_div='".$this->input->post('flag_div')."' ";
				if($id){$where .=" AND A.id=".$id;}
				$sql="SELECT A.*,B.jenis_pembelian,B.jenis_pembayaran,B.cara_pembayaran,B.termin_1,B.termin_2,B.termin_3,
						B.keterangan,B.tot_nilai_faktur,B.potongan,B.id as id_pembayaran,A.id as id_faktur 
						FROM tbl_faktur_pembelian A
						LEFT JOIN tbl_pembayaran_fakturpembelian B ON B.tbl_faktur_pembelian_id=A.id ".$where;
						
				if($id){return $this->db->query($sql)->row();}
						
			break;
			
			case "penerimaan":
				$id=$this->input->post('id');
				$where .=" AND A.flag_div='".$this->input->post('flag_div')."' ";
				if($id){$where .="  AND A.id=".$id;}
				/*$sql="SELECT A.*,B.no_surat_jalan,B.diterima_oleh,B.keterangan,B.tgl_surat_jalan,B.total_truck,
						C.nama_gudang,B.id as id_detil_penerimaan,D.no_pemesanan,D.no_permintaan,D.tgl_pemesanan,D.tgl_permintaan,
						L.nama_dealer,M.nama_group_dealer
						FROM tbl_penerimaan_barang A
						LEFT JOIN tbl_detil_penerimaan B ON B.tbl_penerimaan_barang=A.id
						LEFT JOIN tbl_master_gudang C ON A.tbl_master_gudang_id=C.id 
						LEFT JOIN tbl_master_pemesananbarang D ON A.tbl_master_pemesananbarang_id=D.id 
						LEFT JOIN tbl_master_karyawan E ON D.id_karyawan=E.id 
						LEFT JOIN tbl_master_dealer L ON E.id_dealer=L.id
						LEFT JOIN idx_group_dealer M ON E.id_group_dealer=M.id
						".$where;
					*/
					$sql="SELECT A.*,B.no_surat_jalan,B.diterima_oleh,B.keterangan,B.tgl_surat_jalan,B.total_truck,B.no_plat_armada,
						C.nama_gudang,B.id as id_detil_penerimaan,N.no_pemesanan,G.no_pemesanan as no_permintaan,N.tgl_pemesanan,G.tgl_pemesanan as tgl_permintaan,D.no_faktur_pembelian,
						L.nama_dealer,M.nama_group_dealer
						FROM tbl_penerimaan_barang A
						LEFT JOIN tbl_detil_penerimaan B ON B.tbl_penerimaan_barang=A.id
						LEFT JOIN tbl_master_gudang C ON A.tbl_master_gudang_id=C.id 
						LEFT JOIN tbl_faktur_pembelian D ON A.tbl_faktur_pembelian_id=D.id 
						LEFT JOIN tbl_pemesanan_supplier N ON D.tbl_pemesanan_supplier_id=N.id
						LEFT JOIN tbl_pemesanan_cabang G ON N.tbl_pemesanan_cabang_id=G.id
						LEFT JOIN tbl_master_dealer L ON G.id_dealer=L.id
						LEFT JOIN idx_group_dealer M ON L.id_group_dealer=M.id ".$where;
				if($id){return $this->db->query($sql)->row();}
			break;
			
			
			case "retur":
				$id=$this->input->post('id');
				$where .=" AND A.flag_div='".$this->input->post('flag_div')."' ";
				if($id){$where .="  AND A.id=".$id;}
				/*$sql="SELECT A.*,B.no_surat_jalan,B.diterima_oleh,B.keterangan,B.tgl_surat_jalan,B.total_truck,
						C.nama_gudang,B.id as id_detil_penerimaan,N.no_faktur_pembelian,N.tgl_faktur,
						L.nama_dealer,M.nama_group_dealer
						FROM tbl_retur_pembelian A
						LEFT JOIN tbl_detil_penerimaan B ON B.tbl_penerimaan_barang=A.id
						LEFT JOIN tbl_master_gudang C ON A.tbl_master_gudang_id=C.id 
						LEFT JOIN tbl_master_dealer L ON A.tbl_master_dealer_id=L.id
						LEFT JOIN idx_group_dealer M ON A.tbl_master_group_dealer_id=M.id
						LEFT JOIN tbl_faktur_pembelian N ON A.tbl_faktur_pembelian_id=N.id
						".$where;
				*/
				$sql="SELECT A.*,N.no_faktur_pembelian,N.tgl_faktur
						FROM tbl_retur_pembelian A
						LEFT JOIN tbl_faktur_pembelian N ON A.tbl_faktur_pembelian_id=N.id ".$where;
				if($id){return $this->db->query($sql)->row();}
			break;
			
			
			case "detil_penerimaan":
			
				$sql="SELECT A.*,B.nama_group_barang,C.kategori_barang,D.nominal_harga,D.ppn,Z.nama_barang,E.no_penerimaan,
				Z.merek_barang,Z.satuan_barang
				FROM tbl_subdetil_penerimaan A
				LEFT JOIN tbl_master_barang Z ON A.id_barang=Z.id
				LEFT JOIN idx_group_barang B ON Z.id_group_barang=B.id
				LEFT JOIN idx_kategori_barang C ON Z.id_kategori_barang=C.id
				LEFT JOIN (SELECT * FROM tbl_master_harga_barang WHERE type_harga=2)D ON D.id_barang=Z.id
				LEFT JOIN tbl_penerimaan_barang E ON A.tbl_penerimaan_barang_id=E.id
			
				WHERE E.no_penerimaan='".$this->input->post('no_penerimaan')."'";
				
				return $this->db->query($sql)->result_array();
			break;
			
			case "tbl_subdetil_penerimaan":
				$sql="SELECT A.*,B.nama_group_barang,C.kategori_barang,D.nominal_harga,D.ppn,Z.nama_barang,E.no_penerimaan,
				Z.merek_barang,Z.satuan_barang
				FROM tbl_subdetil_penerimaan A
				LEFT JOIN tbl_master_barang Z ON A.tbl_master_barang_id=Z.id
				LEFT JOIN idx_group_barang B ON Z.id_group_barang=B.id
				LEFT JOIN idx_kategori_barang C ON Z.id_kategori_barang=C.id
				LEFT JOIN (SELECT * FROM tbl_master_harga_barang WHERE type_harga=2)D ON D.id_barang=Z.id
				LEFT JOIN tbl_penerimaan_barang E ON A.tbl_penerimaan_barang_id=E.id
			
				WHERE E.id='".$this->input->post('id')."'";
			
			break;
			case "data_detil_penerimaan":
				$sql="select A.*,case when E.qty_diterima is null then 0 else E.qty_diterima end as qty_diterima ,
					(A.qty-case when E.qty_diterima is null then 0 else E.qty_diterima end)as qty_sisa,
					Z.nama_barang,z.satuan_barang,D.id as id_penerimaan,E.id as id_subdetil,F.nama_gudang,F.id AS id_gudang,
					G.nominal_harga,G.kondisi_harga
					from tbl_detil_fakturpembelian A
					LEFT JOIN tbl_faktur_pembelian B ON A.tbl_faktur_pembelian_id=B.id
					LEFT JOIN tbl_penerimaan_barang D ON D.tbl_faktur_pembelian_id=B.id
					LEFT JOIN tbl_subdetil_penerimaan E ON E.tbl_penerimaan_barang_id=D.id AND E.tbl_master_barang_id=A.tbl_master_barang_id 
					LEFT JOIN tbl_master_barang Z ON A.tbl_master_barang_id=Z.id
					LEFT JOIN tbl_master_gudang F ON D.tbl_master_gudang_id=F.id
					LEFT JOIN (SELECT * FROM tbl_master_harga_barang WHERE type_harga=2)AS G ON (A.tbl_master_barang_id=G.id_barang AND Z.id=G.id_barang) 
					".$where." AND A.tbl_faktur_pembelian_id=".($p2!='' ? $p2 : $this->input->post('id'));
					//echo $sql;exit;
					return $this->db->query($sql)->result_array();
			break;
			
			case "gudang":
				$sql="SELECT * FROM tbl_master_gudang";
				return $this->db->query($sql)->result_array();
			break;
			case "group_dealer":
				$sql="SELECT * FROM idx_group_dealer";
				return $this->db->query($sql)->result_array();
			break;
			case "dealer":
				$sql="SELECT * FROM tbl_master_dealer";
				return $this->db->query($sql)->result_array();
			break;
			
			case "faktur":
					$sql="SELECT A.*,B.jenis_pembelian,B.jenis_pembayaran,B.cara_pembayaran,B.termin_1,B.termin_2,B.termin_3,B.dpp,
						B.keterangan,B.tot_nilai_faktur,B.potongan,B.id as id_pembayaran,C.id as id_pemesanan, 
						C.no_pemesanan,C.tgl_pemesanan,G.no_pemesanan as no_permintaan,G.tgl_pemesanan as tgl_permintaan,E.nama_dealer,F.nama_group_dealer
						FROM tbl_faktur_pembelian A 
						LEFT JOIN tbl_pembayaran_fakturpembelian B ON B.tbl_faktur_pembelian_id=A.id
						LEFT JOIN tbl_pemesanan_supplier C ON A.tbl_pemesanan_supplier_id=C.id
						LEFT JOIN tbl_pemesanan_cabang G ON C.tbl_pemesanan_cabang_id=G.id
						LEFT JOIN tbl_master_dealer E ON G.id_dealer=E.id
						LEFT JOIN idx_group_dealer F ON E.id_group_dealer=F.id
						WHERE A.id=".$this->input->post('id');
					return $this->db->query($sql)->row();
			break;
			case "detil_faktur":
					$sql="SELECT A.*,B.nama_group_barang,C.kategori_barang,D.nominal_harga,D.ppn,Z.nama_barang,
							Z.merek_barang,Z.satuan_barang,(A.qty*D.nominal_harga) as tot_harga,(A.qty*D.nominal_harga)+D.ppn as tot_ppn
							FROM tbl_detil_fakturpembelian A
							LEFT JOIN tbl_master_barang Z ON A.tbl_master_barang_id=Z.id
							LEFT JOIN idx_group_barang B ON Z.id_group_barang=B.id
							LEFT JOIN idx_kategori_barang C ON Z.id_kategori_barang=C.id
							LEFT JOIN (SELECT * FROM tbl_master_harga_barang WHERE type_harga=2)D ON D.id_barang=Z.id
						WHERE A.tbl_faktur_pembelian_id=".$this->input->post('id');
					return  $this->db->query($sql)->result_array();
			break;
			
			case "get_nopemesanan":
				$no=1;
				$sql="SELECT max(SUBSTR(no_pemesanan,5,6))+1 AS no_baru 
						FROM tbl_pemesanan_cabang";
				$res=$this->db->query($sql);
				if(count($res->result_array())>0){
					$no=$res->row('no_baru');
				}
				return $no;
			break;
			case "get_nopemesanan_sup":
				$no=1;
				$sql="SELECT max(SUBSTR(no_pemesanan,5,6))+1 AS no_baru 
						FROM tbl_pemesanan_supplier";
				$res=$this->db->query($sql);
				if(count($res->result_array())>0){
					$no=$res->row('no_baru');
				}
				return $no;
			break;
			
			case "get_noretur":
				$no=1;
				$sql="SELECT max(SUBSTR(no_retur,5,6))+1 AS no_baru 
						FROM tbl_retur_pembelian";
				$res=$this->db->query($sql);
				if(count($res->result_array())>0){
					$no=$res->row('no_baru');
				}
				return $no;
			break;
			
			case "get_nopenerimaan":
				$no=1;
				$sql="SELECT max(SUBSTR(no_penerimaan,5,6))+1 AS no_baru 
						FROM tbl_penerimaan_barang";
				$res=$this->db->query($sql);
				if(count($res->result_array())>0){
					$no=$res->row('no_baru');
				}
				return $no;
			break;
			
			case "list_detil_pemesanan":
				$sts=$this->input->post('sts');
				$where =" WHERE 1=1 ";
				if($sts=='add'){
					$where .=" AND A.id_tbl_master_pemesananbarang=-1";
				}
				$sql="SELECT A.*,C.nama_barang FROM tbl_detil_pemesananbarang A
					LEFT JOIN tbl_master_pemesananbarang B ON A.id_tbl_master_pemesananbarang=B.id
					LEFT JOIN idx_master_barang C ON A.kode_barang=C.kode_barang ".$where;
			break;
			
			//case "list_barang":
			//	$sql="";
			//break;
			
			case "data_karyawan":
				$data=array();
				$sql="SELECT A.*,B.nama_dealer,C.nama_divisi,D.nama_departemen,E.status_karyawan,F.nama_group_dealer 
						FROM tbl_master_karyawan A
						LEFT JOIN tbl_master_dealer B ON A.kode_dealer=B.kode_dealer
						LEFT JOIN idx_divisi C ON A.id_divisi=C.id_divisi
						LEFT JOIN idx_departemen D ON A.id_departemen=D.id_departemen
						LEFT JOIN idx_status_karyawan E ON A.id_status_karyawan=E.id_status_karyawan 
						LEFT JOIN idx_group_dealer F ON B.kode_group_dealer=F.kode_group_dealer
					 WHERE A.nik_karyawan='".$this->input->post('nik')."'";
					 
				$res=$this->db->query($sql);
				$jml=count($res->result_array());
				if($jml > 0){
					$row=$res->row();
					$data=array('nama'=>$row->nama_karyawan,
								'jabatan'=>$row->jabatan,
								'departemen'=>$row->nama_departemen,
								'divisi'=>$row->nama_divisi,
								'dealer'=>$row->nama_dealer,
								'group_dealer'=>$row->nama_group_dealer,
								'msg'=>1
					);
					
					
				}
				else{$data['msg']=0;}
				return json_encode($data);
			break;
			
			case "data_supplier":
				$data=array();
				$sql="SELECT A.*,B.nama_group_supplier,D.nama_provinsi,E.nama_kabupaten,F.nama_kecamatan,G.nama_kelurahan 
						FROM tbl_master_supplier A
						LEFT JOIN idx_group_supplier B ON A.kode_group_supplier=B.kode_group_supplier
						LEFT JOIN tbl_master_wilayah C ON A.id_wilayah=C.id
						LEFT JOIN idx_wilayah_provinsi D ON C.kode_provinsi=D.kode_provinsi
						LEFT JOIN idx_wilayah_kabupaten E ON C.kode_kabupaten=E.kode_kabupaten
						LEFT JOIN idx_wilayah_kecamatan F ON C.kode_kecamatan=F.kode_kecamatan
						LEFT JOIN idx_wilayah_kelurahan G ON C.kode_kelurahan=G.kode_kelurahan
					 WHERE A.kode_supplier='".$this->input->post('kd_supplier')."'";
					 
				$res=$this->db->query($sql);
				$jml=count($res->result_array());
				if($jml > 0){
					$row=$res->row();
					$data=array('nama'=>$row->nama_supplier,
								'kota'=>'Prov '.$row->nama_provinsi. ' KabKota '.$row->nama_kabupaten,
								'alamat'=>$row->alamat,
								'telp'=>$row->telepon,
								'email'=>$row->email,
								
								'msg'=>1
					);
					
					
				}
				else{$data['msg']=0;}
				return json_encode($data);
			break;
			case "no_pemesanan_supplier":
				$sql="SELECT Z.no_pemesanan as no_permintaan,Z.tgl_pemesanan as tgl_permintaan,
					A.id as id_pemesanan_supplier,A.id,A.no_pemesanan,A.tgl_pemesanan,B.nama_supplier,
					G.nama_kabupaten,H.nama_kecamatan,
					I.nama_kelurahan,B.alamat,B.no_telp,B.email,A.pemesanan_via,A.sistem_pengiriman,A.flag,Z.id as id_cabang,A.kode_supplier,J.nama_dealer,K.nama_group_dealer,
					CASE WHEN A.flag_div='U' THEN 'Divisi Unit'
					WHEN A.flag_div='S' THEN 'Divisi Spare Part'
					WHEN A.flag_div='M' THEN 'Divisi Marketing'
					ELSE 'Divisi Not Member'
					END AS DIVISI
					FROM tbl_pemesanan_cabang Z 
					LEFT JOIN tbl_pemesanan_supplier A ON A.tbl_pemesanan_cabang_id=Z.id 
					LEFT JOIN tbl_master_supplier B ON A.kode_supplier=B.id
					LEFT JOIN idx_group_supplier D ON B.id_group_supplier=D.id
					LEFT JOIN tbl_master_wilayah E ON B.id_wilayah=E.id
					LEFT JOIN idx_wilayah_provinsi F ON E.id_provinsi=F.id
					LEFT JOIN idx_wilayah_kabupaten G ON E.id_kabupaten=G.id_kabupaten
					LEFT JOIN idx_wilayah_kecamatan H ON E.id_kecamatan=H.id
					LEFT JOIN idx_wilayah_kelurahan I ON E.id_kelurahan=I.id
					LEFT JOIN tbl_master_dealer J ON Z.id_dealer=J.id
					LEFT JOIN idx_group_dealer K ON J.id_group_dealer=K.id
					".$where." AND  A.flag='P'" ;
			break;
			case "no_faktur":
				$join ="";
				$select ="";
				$modul=$this->input->post('modul');
				if($modul=='retur'){
					$where .=" AND A.flag='F'";
					$select .=",J.nama_gudang,J.id as gudang_id,I.id as penerimaan_id ";
					$join .=" LEFT JOIN tbl_penerimaan_barang I ON I.tbl_faktur_pembelian_id=A.id
							  LEFT JOIN tbl_master_gudang J ON I.tbl_master_gudang_id=J.id ";
				}
				else{$where .=" AND A.flag='P'";}
				$sql="SELECT A.*,B.jenis_pembelian,B.jenis_pembayaran,B.cara_pembayaran,B.termin_1,B.termin_2,B.termin_3,B.dpp,
						B.keterangan,B.tot_nilai_faktur,B.potongan,B.id as id_pembayaran,C.id as id_pemesanan, 
						C.no_pemesanan,C.tgl_pemesanan,G.no_pemesanan as no_permintaan,G.tgl_pemesanan as tgl_permintaan,E.nama_dealer,
						F.nama_group_dealer,H.nama_supplier,C.sistem_pengiriman,C.pemesanan_via,
						H.alamat,H.email,H.no_telp,K.nama_provinsi".$select."
						FROM tbl_faktur_pembelian A 
						LEFT JOIN tbl_pembayaran_fakturpembelian B ON B.tbl_faktur_pembelian_id=A.id
						LEFT JOIN tbl_pemesanan_supplier C ON A.tbl_pemesanan_supplier_id=C.id
						LEFT JOIN tbl_pemesanan_cabang G ON C.tbl_pemesanan_cabang_id=G.id
						LEFT JOIN tbl_master_dealer E ON G.id_dealer=E.id
						LEFT JOIN tbl_master_supplier H ON C.kode_supplier=H.id
						LEFT JOIN idx_group_dealer F ON E.id_group_dealer=F.id 
						LEFT JOIN idx_wilayah_provinsi K ON H.id_wilayah=K.id
						".$join.$where;
				
			break;
		}
		
		
		
		return $this->mdealer->get_json($sql); 
	}
	
	function simpan($p1="",$post=""){
		$this->db->trans_begin();
		$sts=$post['sts'];
		unset($post['sts']);
		switch($p1){
			case "tbl_pemesanan_supplier":
				//print_r($post);exit;
				$id=$post['id'];
				$id_barang=$post['id_barang'];
				$qty=$post['qty'];
				$qty_cabang=$post['qty_cabang'];
				$sts_PPN=$post['sts_PPN'];
				$id_detil=$post['id_detil'];
				$sts_detil=$post['sts_detil'];
				unset($post['id_barang']);unset($post['qty']);unset($post['sts_PPN']);unset($post['id']);unset($post['id_detil']);unset($post['sts_detil']);unset($post['qty_cabang']);
				$post['flag']='P';
				$post['create_by']=$this->auth['fullname'];
				$post['create_date']=date('Y-m-d H:i:s');
				$post['tgl_pemesanan']=date('Y-m-d H:i:s');
				if($sts=='add'){
					$this->db->insert($p1,$post);
					$id = $this->db->insert_id();
					
				}
				else{
					$this->db->where('id',$id);
					$this->db->update($p1,$post);
				}
				$flag_det='F';
				$flag_cab='F';
				if(count($id_barang)>0){
						$jml=count($id_barang);
						for($i=0;$i<$jml;$i++){
							//echo $id_barang[$i].'<br>';
							if($qty!=''){
								if($qty_cabang!=$qty){
									$flag_cab='C';
								}
							
								$data_detail=array('tbl_master_pemesanan_supplier'=>$id,
												   'tbl_master_barang_id'=>$id_barang[$i],
												   'qty'=>$qty[$i],
												   'sts_PPN'=>$sts_PPN,
												   'create_date'=>date('Y-m-d h:s:i'),
												   'create_by'=>$this->auth['fullname'],
												   'flag'=>'P'
								);
								if($sts_detil[$i]=='add')$this->db->insert('tbl_detil_pemesanan_supplier',$data_detail);
								else {$this->db->where('id',$id_detil[$i]);$this->db->update('tbl_detil_pemesanan_supplier',$data_detail);$flag_cab='R';}
							}
						}
						
						$sql="update tbl_pemesanan_cabang set flag='".$flag_cab."' 
							WHERE id=".$this->input->post('tbl_pemesanan_cabang_id');
						$this->db->query($sql);
						$sql="update tbl_detil_pemesanan_cabang set flag='".$flag_cab."' 
							WHERE tbl_pemesanan_cabang_id=".$this->input->post('tbl_pemesanan_cabang_id');
						$this->db->query($sql);
						
						//exit;
					}	
				
			break;
			case "tbl_pemesanan_cabang":
				//print_r($post);exit;
				$id=$post['id'];
				$id_barang=$post['id_barang'];
				$qty=$post['qty'];
				$id_detil=$post['id_detil'];
				$sts_detil=$post['sts_detil'];
				unset($post['id_barang']);unset($post['qty']);unset($post['id']);unset($post['id_detil']);unset($post['sts_detil']);
				$post['flag']='P';
				$post['create_by']=$this->auth['fullname'];
				$post['create_date']=date('Y-m-d H:i:s');
				$post['tgl_pemesanan']=date('Y-m-d H:i:s');
				
				if($sts=='add'){
					$this->db->insert($p1,$post);
					$id = $this->db->insert_id();
					
				}
				else{
					$this->db->where('id',$id);
					$this->db->update($p1,$post);
				}
				
				if(count($id_barang)>0){
						$jml=count($id_barang);
						for($i=0;$i<$jml;$i++){
							//echo $id_barang[$i].'<br>';
							$data_detail=array('tbl_pemesanan_cabang_id'=>$id,
											   'tbl_master_barang_id'=>$id_barang[$i],
											   'flag'=>'P',
											   'qty'=>$qty[$i]
											   
							);
							if($sts_detil[$i]=='add')$this->db->insert('tbl_detil_pemesanan_cabang',$data_detail);
							else {$this->db->where('id',$id_detil[$i]);$this->db->update('tbl_detil_pemesanan_cabang',$data_detail);}
						}
						//exit;
					}	
				
			break;
			case "tbl_faktur_pembelian":
				//print_r($post);exit;
				$id=$post['id'];
				$id_bayar=$post['id_bayar'];
				$id_barang=array();
				if($sts=='add'){
					$id_detil=$post['id_detil'];
					$sts_detil=$post['sts_detil'];
					$id_barang=$post['id_barang'];
					$sts_detil=$post['sts_detil'];
					$qty=$post['qty'];
				}
				
				
				$data_header=array('no_faktur_pembelian'=>$post['no_faktur_pembelian'],
								   'tgl_faktur'=>$post['tgl_faktur'],
								   'tbl_pemesanan_supplier_id'=>$post['tbl_pemesanan_supplier_id'],
								   'create_date'=>date('Y-m-d H:i:s'),
								   'create_by'=>$this->auth['fullname'],
								   'flag'=>'P',
								   'flag_div'=>$post['flag_div']
				);
				
				$data_bayar=array( 'jenis_pembelian'=>$post['jenis_pembelian'],
								   'jenis_pembayaran'=>$post['jenis_pembayaran'],
								   'cara_pembayaran'=>$post['cara_pembayaran'],
								   'termin_1'=>$post['termin_1'],
								   'termin_2'=>$post['termin_2'],
								   'termin_3'=>$post['termin_3'],
								   'keterangan'=>$post['keterangan'],
								   'tot_nilai_faktur'=>($post['tot_nilai_faktur']=='' ? 0 : $post['tot_nilai_faktur']),
								   'potongan'=>($post['potongan']=='' ? 0 : $post['potongan']),
								   'dpp'=>($post['dpp']=='' ? 0 : $post['dpp'])
				);
				
				
				if($sts=='add'){
					$this->db->insert($p1,$data_header);
					$data_bayar["tbl_faktur_pembelian_id"] = $this->db->insert_id();
					$this->db->insert('tbl_pembayaran_fakturpembelian',$data_bayar);
					$sql="update tbl_pemesanan_supplier set flag='F' WHERE id=".$this->input->post('tbl_pemesanan_supplier_id');
					$this->db->query($sql);
					
				}
				else{
					$this->db->where('id',$id);
					$data_bayar["tbl_faktur_pembelian_id"]=$id;
					$this->db->update($p1,$data_header);
					
					$this->db->where('id',$id_bayar);
					$this->db->update('tbl_pembayaran_fakturpembelian',$data_bayar);
					
					
				}
				
				if(count($id_barang)>0){
						$jml=count($id_barang);
						for($i=0;$i<$jml;$i++){
							//echo $id_barang[$i].'<br>';
							$data_detail=array('tbl_faktur_pembelian_id'=>$data_bayar["tbl_faktur_pembelian_id"],
											   'tbl_master_barang_id'=>$id_barang[$i],
											   'create_date'=>date('Y-m-d H:i:s'),
											   'create_by'=>$this->auth['fullname'],
											   'qty'=>$qty[$i],
											   'flag'=>'P'
							);
							if($sts_detil[$i]=='add')$this->db->insert('tbl_detil_fakturpembelian',$data_detail);
							else {$this->db->where('id',$id_detil[$i]);$this->db->update('tbl_detil_fakturpembelian',$data_detail);}
						}
						//exit;
					}	
				
				
				
			break;
			
			case "tbl_penerimaan_barang":
				//print_r($post);exit;
				$id=$post['id'];
				//$id_bayar=$post['id_bayar'];
				$id_detil_penerimaan=$post['id_detil_penerimaan'];
				$id_detil=$post['id_detil'];
				$sts_detil=$post['sts_detil'];
				$id_barang=$post['id_barang'];
				$qty_faktur=$post['qty_faktur_na'];
				$qty=$post['qty'];
				$qty_diterima=$post['qty_diterima'];
				$qty_sisa=$post['qty_sisa'];
				$sts_detil=$post['sts_detil'];
				$data_header=array('no_penerimaan'=>$post['no_penerimaan'],
								   'tgl_penerimaan'=>$post['tgl_penerimaan'],
								   'tbl_faktur_pembelian_id'=>$post['tbl_faktur_pembelian_id'],
								   'tbl_master_gudang_id'=>$post['tbl_master_gudang_id'],
								   'create_date'=>date('Y-m-d H:i:s'),
								   'flag_div'=>$post['flag_div'],
								   'create_by'=>$this->auth['fullname']
				);
				
				$data_detil=array( 'no_surat_jalan'=>$post['no_surat_jalan'],
								   'tgl_surat_jalan'=>$post['tgl_surat_jalan'],
								   'diterima_oleh'=>$post['diterima_oleh'],
								   'total_truck'=>($post['total_truck']=='' ? 0 : $post['total_truck']),
								   'no_plat_armada'=>$this->db->escape_str($post['no_plat_armada']),
								 
								   'keterangan'=>$this->db->escape_str($post['keterangan']),
								
				);
				
				
				if($sts=='add'){
					$this->db->insert($p1,$data_header);
					$data_detil["tbl_penerimaan_barang"] = $this->db->insert_id();
					$this->db->insert('tbl_detil_penerimaan',$data_detil);
					$sql="update tbl_faktur_pembelian set flag='F' WHERE id=".$post['tbl_faktur_pembelian_id'];
					$this->db->query($sql);
					
				}
				else{
					$this->db->where('id',$id);
					$data_detil["tbl_penerimaan_barang"]=$id;
					$this->db->update($p1,$data_header);
					
					$this->db->where('id',$id_detil_penerimaan);
					$this->db->update('tbl_detil_penerimaan',$data_detil);
					
					
				}
				
				if(count($id_barang)>0){
						$jml=count($id_barang);
						//print_r($qty_faktur);
						//echo count($id_barang);
						for($i=0;$i<$jml;$i++){
							//echo $i.'=>'.$qty_faktur[$i];
							//echo $qty_faktur[1];
							//echo $id_barang[$i].'<br>';
							if($sts_detil[$i]=='add'){
								$qty_faktur_na=(int)$qty_faktur[$i];
								$qty_asup=(int)$qty[$i];
								$qty_sisa=($qty_faktur_na-$qty_asup);
							}
							else{
								$qty_faktur_na=(int)$qty_faktur[$i];
								$qty_asup=(int)$qty_diterima[$i]+(int)$qty[$i];
								$qty_sisa=($qty_faktur_na-$qty_asup);
							}
							if($qty_asup==$qty_faktur_na)$flag_header='R';//RETURN FLAG
							else $flag_header='C';//CATATAN QTY BLUM SEMUA DATANG
							$data_detail=array('tbl_penerimaan_barang_id'=>$data_detil["tbl_penerimaan_barang"],
											   'tbl_master_barang_id'=>$id_barang[$i],
											   'qty'=>$qty_faktur_na,
											   'qty_diterima'=>$qty_asup ,
											   'qty_sisa'=>$qty_sisa,
											   'create_date'=>date('Y-m-d'),
								  			   'create_by'=>$this->auth['fullname']
							);
							//print_r($data_detail);
							if($sts_detil[$i]=='add'){
								$this->db->insert('tbl_subdetil_penerimaan',$data_detail);
							}
							else {$this->db->where('id',$id_detil[$i]);$this->db->update('tbl_subdetil_penerimaan',$data_detail);}
							
							//Transfer To Stok
							$cek=$this->db->get_where('tbl_master_stok',array('tbl_master_barang_id'=>$id_barang[$i]))->row();
							if(isset($cek->id)){
								$sql="update tbl_master_stok set qty=".$qty_asup." where id=".$cek->id;
								$this->db->query($sql);
							}
							else{
								$data_stok=array('tbl_master_barang_id'=>$id_barang[$i],
											   'qty'=>$qty_asup,
											   'update_date'=>date('Y-m-d H:i:s'),
								  			   'update_by'=>$this->auth['fullname']);
								$this->db->insert('tbl_master_stok',$data_stok);
							}
							
							
							
						}
							
							$sql="update tbl_penerimaan_barang set flag='".$flag_header."' WHERE id=".$data_detil["tbl_penerimaan_barang"];
							$this->db->query($sql);
							
						//exit;
					}	
				
				
			break;
			case "tbl_subdetil_item_penerimaan":
				 //print_r($post);exit;
				 $sts_detil=$post['sts_detil'];
				if(count($post['cl_warna_id'])>0){
						$jml=count($post['cl_warna_id']);
						for($i=0;$i<$jml;$i++){
							//echo $id_barang[$i].'<br>';
							$data_detail=array('tbl_subdetil_penerimaan_id'=>$post['tbl_subdetil_penerimaan_id'],
											   'cl_warna_id'=>$post['cl_warna_id'][$i],
											   'qty'=>1,
											   'satuan'=>$post['satuan'][$i],
											   'no_mesin'=>$post['no_mesin'][$i],
											   'no_rangka'=>$post['no_rangka'][$i],
											   'tahun'=>$post['tahun'][$i],
											   'create_date'=>date('Y-m-d h:s:i'),
											   'create_by'=>$this->auth['fullname']
							);
							if($post['cl_warna_id'][$i]==''){unset ($data_detail['cl_warna_id']);}
							if($sts_detil[$i]=='add')$this->db->insert('tbl_subdetil_item_penerimaan',$data_detail);
							else {$this->db->where('id',$post['id_detilsub'][$i]);$this->db->update('tbl_subdetil_item_penerimaan',$data_detail);}
						}
						
						//exit;
					}	
			break;
			case "tbl_retur_pembelian":
				//print_r($post);exit;
				$id=$post['id'];
				$post['create_by']=$this->auth['fullname'];
				$post['create_date']=date('Y-m-d H:i:s');
			//	$post['tgl_pemesanan']=date('Y-m-d H:i:s');
				$id_barang=$post['id_barang'];
				$id_detil=$post['id_detil'];
				$sts_detil=$post['sts_detil'];
				$qty=$post['qty'];
				unset($post['id']);
				unset($post['id_barang']);
				unset($post['id_detil']);
				unset($post['sts_detil']);
				unset($post['qty']);
				if($sts=='add'){
					$this->db->insert($p1,$post);
					$id = $this->db->insert_id();
					
				}
				else{
					$this->db->where('id',$id);
					$this->db->update($p1,$post);
				}
				
				if(count($id_barang)>0){
						$jml=count($id_barang);
						for($i=0;$i<$jml;$i++){
							//echo $id_barang[$i].'<br>';
							$data_detail=array('tbl_retur_pembelian_id'=>$id,
											   'tbl_master_barang_id'=>$id_barang[$i],
											   'qty'=>$qty[$i]
											   
							);
							if($sts_detil[$i]=='add')$this->db->insert('tbl_detil_retur_pembelian',$data_detail);
							else {$this->db->where('id',$id_detil[$i]);$this->db->update('tbl_detil_retur_pembelian',$data_detail);}
						}
						//exit;
					}	
				
				
			break;
		}
		
		if($this->db->trans_status() == false){
		$this->db->trans_rollback();
		return "Data not saved";
		} else{
		return $this->db->trans_commit();
		}
	}
	
	function hapus($p1){
		$this->db->trans_begin();
		switch($p1){
			case "pemesanan":
				//print_r($_POST);exit;
				$sql="delete from tbl_detil_pemesanan_supplier where tbl_master_pemesanan_supplier=".$this->input->post('id');
				$this->db->query($sql);
				$sql="delete from tbl_pemesanan_supplier where id=".$this->input->post('id');
				$this->db->query($sql);
				$sql="update tbl_pemesanan_cabang set flag='D' where id=".$this->input->post('id_cabang');
			break;
			case "pemesanan_cabang":
				$sql="delete from tbl_detil_pemesanan_cabang where tbl_pemesanan_cabang_id=".$this->input->post('id');
				$this->db->query($sql);
				$sql="delete from tbl_pemesanan_cabang where id=".$this->input->post('id');
			break;
			
			case "pembelian":
				$sql="delete from tbl_detil_fakturpembelian where tbl_faktur_pembelian_id=".$this->input->post('id');
				$this->db->query($sql);
				$sql="delete from tbl_pembayaran_fakturpembelian where tbl_faktur_pembelian_id=".$this->input->post('id');
				$this->db->query($sql);
				$sql="delete from tbl_faktur_pembelian where id=".$this->input->post('id');
				$this->db->query($sql);
				$sql="update tbl_pemesanan_supplier set flag='D' where id=".$this->input->post('tbl_pemesanan_supplier_id');
			break;
			
		}
		$this->db->query($sql);
		if($this->db->trans_status() == false){
		$this->db->trans_rollback();
		return "Data not saved";
		} else{
		return $this->db->trans_commit();
		}
	}
}	

// end of file mhome.php